﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class MainListDAL : SqlBaseDal
    {
        public EstateDataSet.ListAlertDataTable GetAlerts(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.ListAlertDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_GetAlerts;

                    var parameters = new SqlParameter[7];

                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.NetworkOperatorId;
                    parameters[2] = filterSql.ContractorId;
                    parameters[3] = filterSql.Status;
                    parameters[4] = filterSql.Search;
                    parameters[5] = filterSql.AlertId;
                    parameters[6] = filterSql.Archive;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public DataTable UpdateAlerts(EstateDataSet.AlertChangesRow alert, Int32 userid)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                IFormatProvider datetimeformat = DateTimeFormatInfo.InvariantInfo;
                try
                {
                    WorkflowFilter filter = new WorkflowFilter
                    {
                        AlertId = alert.id,
                        OldStatus = (Int32)alert.oldstatus,
                        NewStatus = (Int32)alert.newstatus,
                        Comments = alert.comments,
                        UserId = userid,
                        TrafficProblems = String.IsNullOrEmpty(alert.trafficproblems) ? false : Boolean.Parse(alert.trafficproblems),                        
                        AlertArea = String.IsNullOrEmpty(alert.area) ? null : (Nullable<Int32>)Int32.Parse(alert.area),
                        //Archive = (Int32)alert.newstatus==Int32.Rejected
                    };
                    //if (alert.newstatus == (Int16)Int32.PartiallyApproved)
                    //{
                    //    filter.EstimateStartDate = String.IsNullOrEmpty(alert.startdate) ? null : (Nullable<DateTime>)DateTime.Parse(alert.startdate, datetimeformat);
                    //    filter.EstimateEndDate = String.IsNullOrEmpty(alert.enddate) ? null : (Nullable<DateTime>)DateTime.Parse(alert.enddate, datetimeformat);
                    //}
                    //else
                    //{
                    //    filter.StartDate = String.IsNullOrEmpty(alert.startdate) ? null : (Nullable<DateTime>)DateTime.Parse(alert.startdate, datetimeformat);
                    //    filter.EndDate = String.IsNullOrEmpty(alert.enddate) ? null : (Nullable<DateTime>)DateTime.Parse(alert.enddate, datetimeformat);
                    //}



                    WorkflowFilterSql filterSql = new WorkflowFilterSql(filter);

                    cmd.CommandText = Constants.Sp_Name_Alert_UpdateDetails;
                    cmd.Parameters.Clear();
                    var parameters = new SqlParameter[14];

                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.NewStatus;
                    parameters[2] = filterSql.OldStatus;
                    parameters[3] = filterSql.EstimateStartDate;
                    parameters[4] = filterSql.EstimateEndDate;
                    parameters[5] = filterSql.AlertArea;
                    parameters[6] = filterSql.StartDate;
                    parameters[7] = filterSql.EndDate;
                    parameters[8] = filterSql.Comments;
                    parameters[9] = filterSql.TrafficProblems;
                    parameters[10] = filterSql.ContractorId;
                    parameters[11] = filterSql.UserId;
                    parameters[12] = filterSql.Archive;
                    parameters[13] = new SqlParameter()
                    {
                        ParameterName = "@actualmaterials",
                        SqlDbType = System.Data.SqlDbType.Structured,
                        Value = new EstateDataSet.UsedMaterialsDataTable()
                    };

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);

                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }

            return result;
        }

        internal EstateDataSet.ContactAlertsDataTable GetContactAlerts(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.ContactAlertsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_GetContactAlerts;

                    var parameters = new SqlParameter[3];

                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.Status;
                    parameters[2] = filterSql.AlertId;
                    


                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public DataTable GetExcelForInvoice(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Invoice_GetExcel;
                    cmd.Parameters.Clear();
                    var parameters = new SqlParameter[3];

                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.Archive;
                    parameters[2] = filterSql.CurrentStatus;
                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);

                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }

            return result;
        }


        internal DataTable GetCsvForInvoice(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Invoice_GetCSV;
                    cmd.Parameters.Clear();
                    var parameters = new SqlParameter[4];

                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.Archive;
                    parameters[2] = filterSql.CurrentStatus;
                    parameters[3] = filterSql.Ids;
                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);

                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }

            return result;
        }

        internal DataTable GetHerstelAlerts(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetHerstelAlerts;
                    cmd.Parameters.Clear();
                    var parameters = new SqlParameter[1];

                    parameters[0] = filterSql.MunicipalityId;                    
                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);

                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }

            return result;
        }
    }
}
